package com.weilan.ark3.sqlite;

import com.weilan.ark3.entity.FengguDto;
import com.weilan.ark3.entity.PVTimeDto;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class SQLPVTimeUtils {

//    public static void isertToDB(PVTimeDto pvTimeDto) {
//        Optional<PVTimeDto> optional = Optional.ofNullable(pvTimeDto);
//        if (optional.isPresent()) {
//            // 对象不为空
//            //获得当前时间的时间戳
//            String time = System.currentTimeMillis() + "";
//            // 可以进行相关操作
//            String startTime = pvTimeDto.getStartTime();
//            String endTime = pvTimeDto.getEndTime();
//            Integer modeState = pvTimeDto.getModeState();
//            Integer runState = pvTimeDto.getRunState();
//            Integer power = pvTimeDto.getPower();
//            String insertSql = "INSERT INTO PVTime (startTime, endTime, modeState, runState, power, timestamp ) VALUES (?, ?, ?, ?, ?, ?);";
//
//            try {
//                Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/fenggu.db");
//                PreparedStatement statement = conn.prepareStatement(insertSql);
//                statement.setString(1, startTime);
//                statement.setString(2, endTime);
//                statement.setInt(3, modeState);
//                statement.setInt(4, runState);
//                statement.setInt(5, power);
//                statement.setString(6, time);
//                statement.executeUpdate();
//                conn.close();
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//            // Object value = optional.get(); // 获取对象的值
//        }
//    }
//
//    public static void deleteToDB(int ID) {
//        //定义一个sql
//        String sql = "DELETE FROM peakValley WHERE timestamp = ?";
//        //判断数据不为空
//        try {
//            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/fenggu.db");
//            PreparedStatement statement = conn.prepareStatement(sql);
//            statement.setInt(1, ID);
//            statement.executeUpdate();
//            conn.close();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }


    public static List<PVTimeDto> query() {
        ArrayList<PVTimeDto> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM PVTime";
        try {
            conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/fenggu.db");
            statement = conn.prepareStatement(sql);
            rs = statement.executeQuery();
            //遍历查询的结果
            while (rs.next()) {
                PVTimeDto pvTimeDto = new PVTimeDto();
                String startTime = rs.getString("startTime");
                String endTime = rs.getString("endTime");
                if(startTime.equals("00:00") && endTime.equals("00:00")){
                    continue;
                }
                pvTimeDto.setStartTime(rs.getString("startTime"));
                pvTimeDto.setEndTime(rs.getString("endTime"));
                pvTimeDto.setModeState(rs.getInt("modeState"));
                pvTimeDto.setRunState(rs.getInt("runState"));
                pvTimeDto.setPower(rs.getInt("power"));
                pvTimeDto.setId(rs.getInt("id"));
                list.add(pvTimeDto);
            }
            statement.executeQuery();
            rs.close();
            statement.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    public static void updateToDB(PVTimeDto pvTimeDto) {
        int power = pvTimeDto.getPower();
        String startTime = pvTimeDto.getStartTime();
        String endTime = pvTimeDto.getEndTime();
        Integer id = pvTimeDto.getId();
        int runState = pvTimeDto.getRunState();
        String sql = "UPDATE PVTime SET startTime = '%s' ,endTime = '%s',runState = '%s', power ='%s' where id = '%s'";
        try {
            Connection conn = DriverManager.getConnection("jdbc:sqlite:C:/db/ARK3/fenggu.db");
            String format = String.format(sql, startTime, endTime, runState, power, id);
            PreparedStatement statement = conn.prepareStatement(format);
            statement.executeUpdate();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
